﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Web;


namespace CCPSIP
{
    public class OracleDataMaintainKit
    {
        /// <summary>取得Oracle連線字串(ISENDER_ORACLE)</summary>
        private static string GetConnStrIsenderOracle()
        {
            return "SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.52.195)(PORT=1521)))(CONNECT_DATA =(SERVER=DEDICATED)(SID=UNIDB)));uid=ISENDER;pwd=aKhJJFA2sWfEsQz0Zm5iwQi9gjiVND";
        }

        /// <summary>增加人員資料到PHONE_HQ資料表中</summary>
        public static bool InsertPhoneHQ(PhoneHQ PersonData)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrIsenderOracle());
            string cmd_all = "INSERT INTO PHONE_HQ(PHONENUM, PPS_CODE, FULLNAME, SECTIONNAME, MAINEXT, CREATETIME)"
                           + " VALUES(:PHONENUM, :PPS_CODE, :FULLNAME, :SECTIONNAME, '1', SYSDATE)";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PHONENUM", OracleType.VarChar).Value = PersonData.Phone_Num;
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PersonData.PPS_Code;
            cmd.Parameters.Add(":FULLNAME", OracleType.VarChar).Value = PersonData.Full_Name;
            cmd.Parameters.Add(":SECTIONNAME", OracleType.VarChar).Value = PersonData.Section_Name;

            ConnOracle.Open();
            int s = cmd.ExecuteNonQuery();
            ConnOracle.Dispose();

            if (s > 0)
                return true;
            else
                return false;
        }

        /// <summary>更新PHONE_HQ資料表中的人員資料</summary>
        public static bool UpdatePhoneHQ(PhoneHQ PersonData)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrIsenderOracle());
            //string cmd_all = "UPDATE PHONE_HQ_TEST" //Test Table
            string cmd_all = "UPDATE PHONE_HQ"
                + " SET PHONENUM = :PHONENUM, PPS_CODE = :PPS_CODE, FULLNAME = :FULLNAME, SECTIONNAME = :SECTIONNAME, MAINEXT = 1, UPDATETIME = SYSDATE"
                + " WHERE PPS_CODE = to_char(:PPS_CODE)";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PHONENUM", OracleType.VarChar).Value = PersonData.Phone_Num;
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PersonData.PPS_Code;
            cmd.Parameters.Add(":FULLNAME", OracleType.VarChar).Value = PersonData.Full_Name;
            cmd.Parameters.Add(":SECTIONNAME", OracleType.VarChar).Value = PersonData.Section_Name;

            ConnOracle.Open();
            int s = cmd.ExecuteNonQuery();
            ConnOracle.Close();

            if (s > 0)
                return true;
            else
                return false;
        }

        /// <summary>刪除PHONE_HQ資料表中的人員資料</summary>
        public static bool DeletePhoneHQ(string PPS_Code)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrIsenderOracle());
            //string cmd_all = "DELETE FROM PHONE_HQ_TEST"  //Test Table
            string cmd_all = "DELETE FROM PHONE_HQ"
                           + " WHERE PPS_CODE = :PPS_CODE";
            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PPS_Code;

            ConnOracle.Open();
            int s = cmd.ExecuteNonQuery();
            ConnOracle.Dispose();

            if (s > 0)
                return true;
            else
                return false;
        }

        /// <summary>檢查PHONE_HQ中是否存在人員資料</summary>
        public static bool CheckPhoneHQ(string PPS_Code)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrIsenderOracle());
            string cmd_all = "SELECT FULLNAME FROM PHONE_HQ"
                           + " WHERE PPS_CODE = :PPS_CODE";

            OracleCommand cmd = new OracleCommand(cmd_all, ConnOracle);
            cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PPS_Code;
            ConnOracle.Open();

            if (string.IsNullOrEmpty(Convert.ToString(cmd.ExecuteScalar())))
            {
                ConnOracle.Dispose();
                return false;
            }
            else
            {
                ConnOracle.Dispose();
                return true;
            }
        }

        ///<summary>檢查iSender帳號是否存在</summary>
        public static bool CheckRegUserExist(string PPS_Code)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrIsenderOracle());

            //【01】找出Status='Y'紀錄的Pid
            string cmd_all = "SELECT PID, SIP_NUM FROM REG_USER_TEST"
                //string cmd_all = "SELECT PID, SIP_NUM FROM REG_USER"
                           + " WHERE STATUS = 'Y'"
                           + " AND PPS_CODE = :PPS_CODE";

            OracleCommand Cmd = new OracleCommand(cmd_all, ConnOracle);
            Cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PPS_Code;

            ConnOracle.Open();
            string s = Convert.ToString(Cmd.ExecuteScalar());
            ConnOracle.Dispose();

            //【99】回傳結果
            if (string.IsNullOrEmpty(s))
                return false;
            else
                return true;
        }

        /// <summary>取得REG_USER中欲更新的紀錄Pid和SipNum</summary>
        public static List<UserSipNumAndPidInOracleRegUserTable> GetDisableRegUserPidAndSipNum(string PPS_Code)
        {
            List<UserSipNumAndPidInOracleRegUserTable> ReturnResult = new List<UserSipNumAndPidInOracleRegUserTable>();
            OracleConnection ConnOracle = new OracleConnection(GetConnStrIsenderOracle());

            //【01】找出Status='Y'紀錄的Pid
            string cmd_all = "SELECT PID, SIP_NUM FROM REG_USER_TEST"
                //string cmd_all = "SELECT PID, SIP_NUM FROM REG_USER"
                           + " WHERE STATUS = 'Y'"
                           + " AND PPS_CODE = :PPS_CODE";

            OracleCommand Cmd = new OracleCommand(cmd_all, ConnOracle);
            Cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PPS_Code;
            ConnOracle.Open();
            OracleDataReader Reader = Cmd.ExecuteReader();

            while (Reader.Read())
            {
                UserSipNumAndPidInOracleRegUserTable u = new UserSipNumAndPidInOracleRegUserTable();
                u.Pid = (Convert.ToInt32(Reader["PID"]));
                u.SipNum = (Convert.ToString(Reader["SIP_NUM"]));
                ReturnResult.Add(u);
            }

            ConnOracle.Dispose();
            return ReturnResult;
        }

        /// <summary>停用iSender帳號</summary>
        public static bool DisableRegUser(string PPS_Code, List<OracleDataMaintainKit.UserSipNumAndPidInOracleRegUserTable> PidAndSipNumList)
        {
            /*
            //【01】找出Status='Y'紀錄的Pid
            string cmd_all = "SELECT PID, SIP_NUM FROM REG_USER_TEST"
                //string cmd_all = "SELECT PID, SIP_NUM FROM REG_USER"
                           + " WHERE STATUS = 'Y'"
                           + " AND PPS_CODE = :PPS_CODE";

            OracleCommand Cmd = new OracleCommand(cmd_all, ConnOracle);
            Cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = PPS_Code;
            ConnOracle.Open();
            OracleDataReader Reader = Cmd.ExecuteReader();

            List<int> Pid = new List<int>();
            List<string> SipNum = new List<string>();
            while (Reader.Read())
            {
                Pid.Add(Convert.ToInt32(Reader["PID"]));
                SipNum.Add(Convert.ToString(Reader["SIP_NUM"]));
            }*/

            //【01】Check PPS_Code
            //if (!CheckRegUserExist(PPS_Code))
            //   return false;

            //【02】GetPidList
            //List<UserSipNumAndPidInOracleRegUserTable> PidAndSipNumList = GetDisableRegUserPidAndSipNum(PPS_Code);

            //【03】更新紀錄, status設成N, DisableTime給值, Remark寫段話紀錄
            OracleConnection ConnOracle = new OracleConnection(GetConnStrIsenderOracle());
            ConnOracle.Open();

            OracleTransaction Trans = ConnOracle.BeginTransaction();

            try
            {
                string cmd_all = "UPDATE REG_USER_TEST"
                    //cmd_all = "UPDATE REG_USER"
                    + " SET STATUS = 'N', DISABLETIME = sysdate, REMARK = 'API_068自動停用'"
                    + " WHERE PID = :PID";

                foreach (UserSipNumAndPidInOracleRegUserTable u in PidAndSipNumList)
                {
                    OracleCommand Cmd = new OracleCommand(cmd_all, ConnOracle, Trans);
                    Cmd.Parameters.Add(":PID", OracleType.Number).Value = u.Pid;
                    Cmd.ExecuteNonQuery();
                }

                Trans.Commit();
            }
            catch (Exception ex)
            {
                if (Trans != null)
                {
                    Trans.Rollback();
                }
                throw ex;
            }
            finally
            {
                ConnOracle.Dispose();
            };

            return true;
        }

        //列舉
        public enum Operation
        { Insert, Update, Delete };

        public enum HttpMethod  //.Net 4.5以上才有內建HttpMethod
        { DELETE, GET, POST, PUT }

        //類別
        public class PhoneHQ
        {
            public string Phone_Num { get; set; }
            public string PPS_Code { get; set; }
            public string Full_Name { get; set; }
            public string Section_Name { get; set; }

            public PhoneHQ(string phone_num, string pps_code, string full_name, string section_name)
            {
                Phone_Num = phone_num;
                PPS_Code = pps_code;
                Full_Name = full_name;
                Section_Name = section_name;
            }

            public PhoneHQ()
            {
            }
        }

        public class UserSipNumAndPidInOracleRegUserTable
        {
            public string SipNum { get; set; }
            public int Pid { get; set; }
        }
    }
}